sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO 
SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO 

USE msdb
GO
EXEC sp_send_dbmail @profile_name='CXT DB Backup',
@recipients='phyokyawthant@gmail.com',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.' 

USE msdb
GO
EXEC sp_send_dbmail @profile_name='CXT DB Backup',
@recipients='phyokyawthant@gmail.com',
@subject='Server 110 - APP DB Backup Afternoon ',
@body='Backup finished. Congrates Database Mail Received By you Successfully.'

Select 'IT OK'

--APP DB
USE CX_TH_DB_Live
--as SMK is instance based, it may be possible that
--you are trying to restore database in different instance
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
--again opening master key before taking up backup
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'CXGlobalSecurityKey'
Declare @strDBAndDate varchar(250)
Declare @strTime varchar(50)
SET @strTime = '_3_30_AM';
Set @strDBAndDate = N'E:\CX_TH_DB_Backup\CX_TH_DB\CX_TH_DB_Live_110@' + Convert(varchar(112), GetDate(), 112) + @strTime + '.bak' 
BACKUP DATABASE [CX_TH_DB_Live] TO  DISK = @strDBAndDate  WITH NOFORMAT, NOINIT,  NAME = N'CX TH DB Live 110 Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
--==========================================================================================================================
USE [master]

Set @strDBAndDate = N'E:\CX_TH_DB_Backup\CX_TH_DB\CX_TH_DB_Provider_Log_Live_110@' + Convert(varchar(112), GetDate(), 112) + @strTime +  '.bak' 
BACKUP DATABASE [CX_TH_DB_Provider_Log_Live] TO  DISK = @strDBAndDate  WITH NOFORMAT, NOINIT,  NAME = N'CX TH DB Provider Log Live 110 Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
--==============================================================================================================================

USE [CX_TH_DB_Report]
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'c0mpar58pr5ssDmKey'
Set @strDBAndDate = N'E:\CX_TH_DB_Backup\CX_TH_DB\CX_TH_DB_Report_110@' + Convert(varchar(112), GetDate(), 112) + @strTime +  '.bak' 
BACKUP DATABASE [CX_TH_DB_Report] TO  DISK = @strDBAndDate  WITH NOFORMAT, NOINIT,  NAME = N'CX TH DB Report 110 Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'c0mpar58pr5ssDmKey'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
--==============================================================================================================================

USE [CX_TH_DB_Payment]
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'CXGlobalSecurityKey'
Set @strDBAndDate = N'E:\CX_TH_DB_Backup\CX_TH_DB\CX_TH_DB_Payment_110@' + Convert(varchar(112), GetDate(), 112) + @strTime +  '.bak' 
BACKUP DATABASE [CX_TH_DB_Payment] TO  DISK = @strDBAndDate  WITH NOFORMAT, NOINIT,  NAME = N'CX TH DB Payment 110 Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'CXGlobalSecurityKey'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
--==============================================================================================================================

--SYSTEM DB
Declare @strMasterDate varchar(200)
Set @strMasterDate = N'E:\CX_TH_DB_Backup\System_DB\master_110@' + Convert(varchar(112), GetDate(), 112) + '_3_00_AM' + '.bak' 
BACKUP DATABASE [master] TO  DISK = @strMasterDate  WITH NOFORMAT, NOINIT,  NAME = N'MASTER Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Declare @strModelDate varchar(200)
Set @strModelDate = N'E:\CX_TH_DB_Backup\System_DB\model_110@' + Convert(varchar(112), GetDate(), 112) + '_3_00_AM' + '.bak' 
BACKUP DATABASE [model]TO  DISK = @strModelDate  WITH NOFORMAT, NOINIT,  NAME = N'MODEL Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Declare @strMSDBDate varchar(200)
Set @strMSDBDate = N'E:\CX_TH_DB_Backup\System_DB\msdb_110@' + Convert(varchar(112), GetDate(), 112) + '_3_00_AM' + '.bak' 
BACKUP DATABASE [model]TO  DISK = @strMSDBDate  WITH NOFORMAT, NOINIT,  NAME = N'MSDB Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO